package com.hnkywl.sts.dao.base;

import com.hnkywl.sts.entity.base.Gspz;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-5-20
 * Time: 下午8:48
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class GspzDao extends SimpleHibernateTemplate<Gspz, Long> {
    public Page<Gspz> findGspzList(String mc, int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Gspz> page = new Page<Gspz>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (mc != null && !mc.isEmpty()) {
            condition.append(" mc like ? ");
            param.add("%" + mc.trim() + "%");
        }
        //==============记录数量===============
        hql = "select count(*) from Gspz";
        if (condition.length() != 0)
            hql = hql + " where " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Gspz ";
        if (condition.length() != 0)
            hql = hql + " where " + condition;
        hql = hql + " order by dateCreated desc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }


    //显示状态为已启用的公式
    public Page<Gspz> findGspzList01(String mc, int pageNo, int pageSize) {
        String hql = null;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Gspz> page = new Page<Gspz>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (mc != null && !mc.isEmpty()) {
            condition.append(" mc like ? ");
            param.add("%" + mc.trim() + "%");
        }

        condition.append(" sfqy != 0  ");

        //==============记录数量===============
        hql = "select count(*) from Gspz";
        if (condition.length() != 0)
            hql = hql + " where " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Gspz ";
        if (condition.length() != 0)
            hql = hql + " where " + condition;
        hql = hql + " order by dateCreated desc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }

    public Page<Map> findgspzListByHtId(Long htId, int pageNo, int pageSize) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        Page<Map> page = new Page<Map>();

        //=====================================
        if (htId != null) {
            condition.add(" b.id = :htId ");
            param.put("htId", htId);
        }
        //==============记录数量===============
        String sqlCountBase = "select count(1) from ";

        //==============查询结果===============
        String sqlBase = "select a.id as \"id\",a.mc as \"mc\", a.jzrq as \"jzrq\",c.xm as \"szr.xm\",a.sfqy as \"sfqy\" from t_sys_gspz a left join t_biz_ht b on a.id = b.gspz_id left join t_sys_yg c on a.szr_id= c.id ";


        String whereSql = "";
        String orderSql = "  order by a.id ";
        if (!condition.isEmpty())
            whereSql += " where " + StringUtils.join(condition, " and ");
        String countSql = sqlCountBase + " ( " + sqlBase + whereSql + " ) ";
        String resultSql = sqlBase + whereSql + orderSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    public Page<Map> findAdsqList(Long gspzId, int pageNo, int pageSize) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        Page<Map> page = new Page<Map>();

        //=====================================
        if (gspzId != null) {
            condition.add(" a.gspz_id = :gspzId ");
            param.put("gspzId", gspzId);
        }
        //==============记录数量===============
        String sqlCountBase = "select count(1) from ";

        //==============查询结果===============
        String sqlBase = "select a.id as \"id\",a.gspz_id as \"gspz.id\",a.startdate as \"startDate\",a.enddate as \"endDate\",a.sfbz as \"sfbz\" from t_sys_adsq a ";


        String whereSql = "";
        String orderSql = "  order by a.id ";
        if (!condition.isEmpty())
            whereSql += " where " + StringUtils.join(condition, " and ");
        String countSql = sqlCountBase + " ( " + sqlBase + whereSql + " ) ";
        String resultSql = sqlBase + whereSql + orderSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    public Page<Map> findAtsqList(Long gspzId, int pageNo, int pageSize) {
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        Page<Map> page = new Page<Map>();

        //=====================================
        if (gspzId != null) {
            condition.add(" a.gspz_id = :gspzId ");
            param.put("gspzId", gspzId);
        }
        //==============记录数量===============
        String sqlCountBase = "select count(1) from ";

        //==============查询结果===============
        String sqlBase = "select a.id as \"id\",z.z as \"fklx.z\",z.id as \"fklx.id\", a.startdate as \"startDate\",a.enddate as \"endDate\",a.llgs as \"llgs\",a.gspz_id as \"gspz.id\" from t_sys_atsq a left join t_sys_sjzdz z on a.fklx_sjzdz_id = z.id  ";


        String whereSql = "";
        String orderSql = "  order by a.id ";
        if (!condition.isEmpty())
            whereSql += " where " + StringUtils.join(condition, " and ");
        String countSql = sqlCountBase + " ( " + sqlBase + whereSql + " ) ";
        String resultSql = sqlBase + whereSql + orderSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    public Boolean checkUnique(Long id, String propertyName, Object propertyValue) {
        String hql;
        if (id == null) {
            //修改检验
            hql = "from Gspz where " + propertyName + " = ? ";
            return this.existsQuery(hql, propertyValue);
        } else {
            //新增检验
            hql = "from Gspz where " + propertyName + " = ? and id <>?";
            return this.existsQuery(hql, propertyValue, id);
        }
    }

    public Boolean checkGspzUsed(Long gspzId) {
        String sql = "select 1 from t_biz_ht a left join t_sys_gspz b on a.gspz_id = b.id  where b.id = ?";
        return this.existsSQLQuery(sql, gspzId);
    }
}
